package entity.connector;

import entity.bean.House;
import entity.bean.Seat;
import entity.bean.Show;
import java.sql.*;

/**
 *
 * @author 52571783
 */
public class SeatDownloader {
    private int rows;
    private int cols;
    private Seat[][] seatPlan;
    
    public SeatDownloader(House house){
        initializeSeatPlan(house);
        updateUnavailability(house);
    }
    
    public SeatDownloader(Show show){
        House house = HouseConnector.getHouse(show);
        initializeSeatPlan(house);
        updateUnavailability(house);
        updateOccupancy(show);
    }

    public Seat[][] getSeatPlan(){
        return seatPlan;
    }
    
    public int getRows(){
        return rows;
    }
    
    public int getCols(){
        return cols;
    }
    
    private void initializeSeatPlan(House house) {
        rows = house.getRows();
        cols = house.getCols();
        seatPlan = new Seat[rows][cols];
        for (int i = 0; i < rows; i++) {
            for (int j = 0; j < cols; j++) {
                seatPlan[i][j] = new Seat(i+1,j+1);
            }
        }
    }
    
    private void updateUnavailability(House house) {
        try {
            // get the required information from database
            Connection con = ConnectionProvider.getCon();
            PreparedStatement ps = con.prepareStatement("SELECT * FROM unavailable_seat WHERE house_id = ? ORDER BY row_no, col_no",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, house.getId());
            ResultSet rs = ps.executeQuery();
            int row, col;
            // store data from result set to static data members
            while (rs.next() != false) {
                row = rs.getInt("row_no");
                col = rs.getInt("col_no");
                seatPlan[row-1][col-1].setUnavailability(true);
            }
            // close resources after use
            rs.close();
            ps.close();
        } catch (Exception e) {
        }
    }

    private void updateOccupancy(Show show) {
        try {
            // get the required information from database
            Connection con = ConnectionProvider.getCon();
            PreparedStatement ps = con.prepareStatement(
                    "SELECT * FROM movie_ticket WHERE movie_id = ? AND movie_time = ? AND refunded = 0 AND (price IS NULL OR "
                    + "DATEDIFF(second,(SELECT purchase_time FROM purchase WHERE purchase.purchase_id = movie_ticket.purchase_id),GETDATE()) < 300)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, show.getMovieID());
            ps.setString(2, TimeConvertor.calendarToString(show.getTime()));
            ResultSet rs = ps.executeQuery();
            int row, col;
            // store data from result set to static data members
            while (rs.next() != false) {
                row = rs.getInt("row_no");
                col = rs.getInt("col_no");
                seatPlan[row-1][col-1].setOccupancy(true);
            }
            // close resources after use
            rs.close();
            ps.close();
        } catch (Exception e) {
        }
    }
}
